create or replace package ac_utils is

  -- Author  : LEV ERUSALIMSKIY
  -- Created : 02/12/2011 11:02:48 PM
  -- Purpose : 
 
  G_AUTH_SCHEMES     constant varchar2(12) := 'AUTH_SCHEMES';
  G_VALIDATIONS      constant varchar2(11) := 'VALIDATIONS';
  G_PROCESS          constant varchar2(7)  := 'PROCESS';
  G_REGION           constant varchar2(6)  := 'REGION';
  G_COMPUTATION      constant varchar2(11) := 'COMPUTATION';
  G_REPORT           constant varchar2(6)  := 'REPORT';
  G_REPORT_PRINT_PARAMETER constant varchar2(22) := 'REPORT_PRINT_PARAMETER';
  G_REPORT_COLUMN    constant varchar2(13) := 'REPORT_COLUMN';
  G_PAGE             constant varchar2(4)  := 'PAGE';
  G_PAGE_ITEMS       constant varchar2(10) := 'PAGE_ITEMS';
  G_APPLICATION_ITEM constant varchar2(16) := 'APPLICATION_ITEM';
  G_APPLICATION      constant varchar2(11) := 'APPLICATION';
  G_BUTTON           constant varchar2(11) := 'BUTTON';
  G_BRANCH           constant varchar2(11) := 'BRANCH';
  G_LOVS             constant varchar2(11) := 'LOV';
  G_DYN_ACTIONS      constant varchar2(11) := 'DYN_ACTIONS';
  G_AUTHORIZATION    constant varchar2(30) := 'APPLICATION_AUTHORIZATION';
  G_INTERACTIVE_REPORT constant varchar2(30) := 'INTERACTIVE_REPORT';
  G_INTERACTIVE_REPORT_COLS constant varchar2(30) := 'INTERACTIVE_REPORT_COLS';
  G_PAGE_PROCESS            constant varchar2(30) := 'PAGE_PROCESS';

  G_CR   constant varchar2(1) := chr(13);
  G_LF   constant varchar2(1) := chr(10);
  
  G_CRLF constant varchar2(2) := G_CR || G_LF;

  function ins_result_msg(p_result_id number,  pi_parent_id number, p_msg varchar2, p_sort number := 1, 
                         p_isbranch boolean  := true, p_object_type varchar2:= null,
                         p_help_text varchar2:= null
                         ) return number; 

  function in1NotIn2(p_msg varchar2, p_app_id_1 varchar2, p_app_id_2 varchar2) return varchar2;

  function getParamName(p_src varchar2, p_max_len number) return varchar2;

  function hs(pc clob) return varchar2 deterministic;

end ac_utils;
/
create or replace package body ac_utils is

gc_hash varchar2(100) := utl_raw.cast_to_varchar2(dbms_crypto.Hash(to_clob('***'),3));

procedure create_session(pi_app_id number, pi_user_name varchar2, pi_session varchar2) is
l_cgivar_name owa.vc_arr; 
l_cgivar_val owa.vc_arr;
--
l_workspace_id number;
l_application_id number := pi_app_id; /* change this to your application ID */
l_user varchar2(30) := pi_user_name; 
 
BEGIN
 
-- set up cgi environment
htp.init;
l_cgivar_name(1) := 'REQUEST_PROTOCOL';
l_cgivar_val(1) := 'HTTP';
owa.init_cgi_env(num_params => 1,param_name => l_cgivar_name,param_val => l_cgivar_val);
 
-- find workspace ID of application
for c1 in (select workspace_id from apex_applications where application_id = l_application_id) loop
l_workspace_id := c1.workspace_id;
end loop;
 
-- set security group ID, session ID, app ID globals
wwv_flow_api.set_security_group_id(l_workspace_id);
apex_application.g_instance := wwv_flow_custom_auth.get_next_session_id;
apex_application.g_flow_id := l_application_id;
 
-- create authenticated session for l_user
wwv_flow_custom_auth_std.post_login(
p_uname => l_user,
p_session_id => apex_application.g_instance,
p_flow_page => apex_application.g_flow_id||':'||1);
 
--apex_util.set_session_state('P168_ID','434');

/* 
wwv_flow.show (
p_flow_id => apex_application.g_flow_id,
p_instance => apex_application.g_instance,
P_FLOW_STEP_ID => '168'
);
*/

wwv_flow_api.set_security_group_id(l_workspace_id); 


--apex_util.set_session_state('P168_ID','435');

 apex_util.set_session_state('P1_APP_ID1','100');
 apex_util.set_session_state('P1_APP_ID2','101');

 --init_res_collection;


END;

function hs(pc clob) return varchar2 
   deterministic
   is
 v_res varchar2(100);   
begin
  if pc is null then
    v_res := gc_hash;
  elsif dbms_lob.getlength(pc) = 0 then
    v_res := gc_hash;
  else  
    v_res := utl_raw.cast_to_varchar2(dbms_crypto.Hash(pc, 3));
  end if;
  return v_res;
end hs;

 function ins_result_msg(p_result_id number,  pi_parent_id number, p_msg varchar2, p_sort number := 1, 
                         p_isbranch boolean  := true, p_object_type varchar2:= null,
                         p_help_text varchar2:= null
                         ) return number is
   v_res_id number;
   v_is_branch varchar2(1); 
   begin     
     if nvl(p_isbranch,true) then 
      v_is_branch := 'Y';
     else
      v_is_branch := 'N';
     end if;           
   
     insert into ac_results
         (result_id, parent_id, param_name, sort,  is_branch, object_type,     help_text, is_src)
     values
         (p_result_id,  pi_parent_id, p_msg, p_sort, v_is_branch, p_object_type, p_help_text, 'N')
     returning id into v_res_id;
       
     return v_res_id;
 end  ins_result_msg;

 function in1NotIn2(p_msg varchar2, p_app_id_1 varchar2, p_app_id_2 varchar2) return varchar2 is
 begin
  return  p_msg ||' in '||p_app_id_1||' not in '||p_app_id_2;   
 end  in1NotIn2;

 function getParamName(p_src varchar2, p_max_len number) return varchar2 as
 v_res varchar2(4000) := p_src;
 begin
    if length(v_res) > p_max_len then
     v_res := substr(v_res, 1, p_max_len)||'...';
    end if;
      
    return v_res;   
 end getParamName;

end ac_utils;
/
